package com.film.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import com.film.dao.ClassifiedFilmDao;
import com.film.domain.Film;
import com.film.util.DBUtil;

public class ClassifiedFilmDaoImpl implements ClassifiedFilmDao {
	
	private JdbcTemplate temp = new JdbcTemplate(DBUtil.getDataScorse());
	public List<Film> selectClassifiedFilm(String title,String types,String area,String years,String sortFlag) {
		
		
		Connection conn = null;
		PreparedStatement pre = null;
		ResultSet res = null;
		//用来装查询到的电影
		List<Film> films = new ArrayList<>();
		try {
			conn = DBUtil.getConnecion();
			

			
			String sql = "select DISTINCT t1.* from film t1,film_type t2,filmtype t3 where t1.film_id = t2.film_id and t2.type_id = t3.type_id and t1.is_delete = 0";
			
			
			//用来装参数
			List<Object> params = new ArrayList<>();
			if("正在热映".equals(title)) {
				sql+="  and t1.expect = ?";
				params.add(0);
			}
			if("即将上映".equals(title)) {
				sql+="  and t1.expect = ?";
				params.add(1);
			}
			if(types!=null && !types.equals("")&& !"全部".equals(types)) {
				sql+="  and t3.type_name = ?";
				params.add(types);
				
			}
			if(area!=null && !area.equals("")&& !"全部".equals(area)) {
				sql+="  and t1.film_place like ?";
				params.add('%'+area+'%');
				
			}
			if(years!=null && !years.equals("")&& !"全部".equals(years)) {
				sql+="  and t1.film_showTime like ?";
				params.add('%'+years+'%');
				
			}
			if("按时间排序".equals(sortFlag)) {
				sql+="  order by film_showTime desc";
				
			}
			if("按热门排序".equals(sortFlag)) {
				sql+="  order by boxoffice desc";
				
			}
			if("按评价排序".equals(sortFlag)) {
				sql+="  order by review_score desc";
				
			}
			System.out.println(sql);
			System.out.println(params);
			pre = conn.prepareStatement(sql);
			for(int i = 0;i<params.size();i++) {
				pre.setObject(i+1,params.get(i));
			}
			res = pre.executeQuery();
			
			while(res.next()) {
				Film film = new Film();
				film.setFilmId(res.getInt("film_id"));
				film.setFilmName(res.getString("film_name"));
				film.setFilmForeignName(res.getString("film_foreignName"));
				film.setFilmPlace(res.getString("film_place"));
				film.setFilmTime(res.getString("film_time"));
				film.setFilmShowTime(res.getString("film_showTime"));
				film.setFilmImg(res.getString("film_img"));
				film.setIsDelete(res.getInt("is_delete"));
				film.setBoxoffice(res.getFloat("boxoffice"));
				film.setExpect(res.getInt("expect"));
				film.setFilmIunguage(res.getString("film_lunguage"));
				film.setFilmIntroduce(res.getString("film_introduce"));
				film.setReviewScore(res.getString("review_score"));
				films.add(film);
				
			}
		
//			System.out.println(sql);
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBUtil.closeAll(res, pre, conn);
		}
		return films;
		
	}
	@Override
	public List<Film> limitSelectClassifiedFilm(String title, String types, String area, String years, int pageNum,
			int stripNum,String sortFlag) {
			Connection conn = null;
			PreparedStatement pre = null;
			ResultSet res = null;
			//用来装查询到的电影
			List<Film> films = new ArrayList<>();
			try {
				conn = DBUtil.getConnecion();
				String sql = "select DISTINCT t1.* from film t1,film_type t2,filmtype t3 where t1.film_id = t2.film_id and t2.type_id = t3.type_id and t1.is_delete = 0";
				
				
				//用来装参数
				List<Object> params = new ArrayList<>();
				if("正在热映".equals(title)) {
					sql+="  and t1.expect = ?";
					params.add(0);
				}
				if("即将上映".equals(title)) {
					sql+="  and t1.expect = ?";
					params.add(1);
				}
				if(types!=null && !types.equals("")&& !"全部".equals(types)) {
					sql+="  and t3.type_name = ?";
					params.add(types);
					
				}
				if(area!=null && !area.equals("")&& !"全部".equals(area)) {
					sql+="  and t1.film_place like ?";
					params.add('%'+area+'%');
					
				}
				if(years!=null && !years.equals("")&& !"全部".equals(years)) {
					sql+="  and t1.film_showTime like ?";
					params.add('%'+years+'%');
					
				}
				if("按时间排序".equals(sortFlag)) {
					sql+="  order by film_showTime desc";
					
				}
				if("按热门排序".equals(sortFlag)) {
					sql+="  order by boxoffice desc";
					
				}
				if("按评价排序".equals(sortFlag)) {
					sql+="  order by review_score desc";
					
				}
				params.add(pageNum);
				params.add(stripNum);
				sql+="  limit ?,?";
				pre = conn.prepareStatement(sql);
				for(int i = 0;i<params.size();i++) {
					pre.setObject(i+1,params.get(i));
				}
				res = pre.executeQuery();
				
				while(res.next()) {
					Film film = new Film();
					film.setFilmId(res.getInt("film_id"));
					film.setFilmName(res.getString("film_name"));
					film.setFilmForeignName(res.getString("film_foreignName"));
					film.setFilmPlace(res.getString("film_place"));
					film.setFilmTime(res.getString("film_time"));
					film.setFilmShowTime(res.getString("film_showTime"));
					film.setFilmImg(res.getString("film_img"));
					film.setIsDelete(res.getInt("is_delete"));
					film.setBoxoffice(res.getFloat("boxoffice"));
					film.setExpect(res.getInt("expect"));
					film.setFilmIunguage(res.getString("film_lunguage"));
					film.setFilmIntroduce(res.getString("film_introduce"));
					film.setReviewScore(res.getString("review_score"));
					films.add(film);
				}
//			
				System.out.println(sql);
				System.out.println(params);
			}catch (Exception e) {
				e.printStackTrace();
			}finally {
				DBUtil.closeAll(res, pre, conn);
			}
			return films;
	}



}
